package com.hualude.roadsign.repository;

import com.hualude.roadsign.dto.DTMachineFaultDto;
import com.hualude.roadsign.dto.DTRequestDto;
import com.hualude.roadsign.dto.SignalMachineFaultDto;
import org.hibernate.query.NativeQuery;
import org.hibernate.transform.Transformers;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.List;

/**
 * Created by pjn on 2017/7/22.
 */
public class SignalMachineFaultRepositoryImpl {
    @PersistenceContext
    private EntityManager em;

    public Integer getMachineReportFaultCount4DT(Integer actorId, Integer userId, Integer orgId, String strSearch) throws Exception {
        String sql = "select count(*) " +
                "FROM (((tb_machine_fault mf JOIN tb_user u ON u.UserId=mf.MachineCreatorId)\n" +
                "JOIN tb_org o ON o.OrgId=mf.MachineOrgId)JOIN tb_signal_machine sm ON sm.SignalMachineId=mf.SignalMachineId)\n" +
                "WHERE concat(sm.SignalMachineStartRoad,sm.SignalMachineEndRoad)like '%" + strSearch + "%'";
        if (actorId == 3) {
            sql += " and mf.MachineOrgId=" + orgId + "";
        } else if (actorId == 6) {
            sql += " and mf.MachineCreatorId=" + userId + "";
        }
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public List<DTMachineFaultDto> getMachineReportFault4DT(DTRequestDto dtRequestDto, Integer actorId, Integer userId, Integer orgId) throws Exception {
       String sql = "SELECT mf.*,u.UserName AS machineCreatorName,u2.UserName AS machineMaintainerName,u3.UserName " +
                "AS machineMainCheckerName, o.OrgName AS machineOrgName,l.SignalMachinePhoto from tb_machine_fault mf left join tb_user u on" +
                " u.UserId=mf.MachineCreatorId left join tb_user u2 on u2.userId = mf.machineMaintainerId " +
                "left join tb_user u3 on u3.userId=mf.MachineMainCheckerId left join tb_org o on o.orgId=mf.MachineOrgId " +
                "left join tb_signal_machine l on l.SignalMachineId=mf.SignalMachineId " +
                "WHERE concat(l.SignalMachineStartRoad,l.SignalMachineEndRoad)like '%" + dtRequestDto.getSearch() + "%' ";

        if (actorId == 3) {
            sql += " and mf.MachineOrgId=" + orgId + "";
        } else if (actorId == 6) {
            sql += " and mf.MachineCreatorId=" + userId + "";
        }
        sql += " AND  mf.MachineCheck IN (0,1,2,-1) \n" +
                " order by find_in_set(  mf.MachineCheck ,'0,2,1,-1') ,mf.MachineTime DESC";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(DTMachineFaultDto.class));
        List<DTMachineFaultDto> machineFaultDtos = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return machineFaultDtos;
    }

    public List<DTMachineFaultDto> getSignalMachineFault4DT(DTRequestDto dtRequestDto, Integer actorId, Integer orgId) throws Exception{
        String sql = "SELECT MachineFaultId,mf.SignalMachineId,MachinePhoto,MachineCreatorId,MachineTime," +
                "MachineCheckerId,machineMoney,machineLevel,machineType,mf.MachineFeedback,mf.MachineReason,\n" +
                "MachineMainCheckerId,MachineCheck,MachineMainCheck,MachineOrgId,MachineMainOrgId,OrgName AS machineOrgName,SignalMachinePhoto \n" +
                "FROM tb_machine_fault mf,tb_signal_machine sm,tb_org o\n" +
                "WHERE mf.MachineOrgId=o.OrgId AND mf.SignalMachineId = sm.SignalMachineId " +
                " AND o.orgId like '%" + dtRequestDto.getSearch() + "%' AND mf.MachineCheck=1 ";
//        根据主管审核人和系统管理员区分
        if (actorId == 7) {
            sql += " AND mf.MachineMainOrgId=" + orgId + "";
        }
        sql += "  AND  mf.MachineMainCheck IN (0,1,-1) \n" +
                " order by  find_in_set( mf.MachineMainCheck ,'0,1,-1') , mf.MachineTime DESC";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(DTMachineFaultDto.class));
        List<DTMachineFaultDto> machineFaultDtos = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return machineFaultDtos;
    }

    public Integer getSignalMachineFaultCount4DT(DTRequestDto dtRequestDto, Integer actorId, Integer orgId) throws Exception{
        String sql = "SELECT count(*)\n" +
                "FROM tb_machine_fault mf,tb_signal_machine sm,tb_org o\n" +
                "WHERE mf.MachineOrgId=o.OrgId AND mf.SignalMachineId = sm.SignalMachineId " +
                " AND o.orgId like '%" + dtRequestDto.getSearch() + "%' AND mf.MachineCheck=1 ";
//        根据主管审核人和系统管理员区分
        if (actorId == 7) {
            sql += " AND mf.MachineMainOrgId=" + orgId + "";
        }
        Query query = em.createNativeQuery(sql);
        return  Integer.parseInt(query.getSingleResult().toString());
    }

    public List<DTMachineFaultDto> getSignalMachine4DT(DTRequestDto dtRequestDto, String actorIds, Integer orgId, Integer machineMaintenanceStatus) throws Exception{
        String sql = "SELECT u.userName as machineCreatorName,MachineFaultId,OrgName AS machineOrgName,MachineCreatorId,MachineTime,MachineCheckerId,MachineType,MachineLevel,MachineMaintenancePhoto,MachineMaintainerId," +
                "MachineReason,MachineMoney,MachineMaintenanceStatus,MachineMainCheckTime,MachineMaintenanceExplain,signalMachineLongitude,signalMachineLatitude," +
                "MachineMainCheckerId,mf.SignalMachineId,MachineMaintenanceContent,MachineFeedback,signalMachineStartRoad,signalMachineEndRoad," +
                "MachinePhoto,SignalMachinePhoto,SignalMachineArea,SignalMachineCity,SignalMachineProvince,MachineOrgId,machineMaintenanceTime\n" +
                "FROM tb_signal_machine sm,tb_machine_fault mf,tb_org o,tb_user u \n" +
                "WHERE mf.SignalMachineId=sm.SignalMachineId AND u.userId=mf.machineCreatorId AND mf.MachineOrgId=o.OrgId AND mf.MachineMainCheck in (1,2) " +
                "AND concat(sm.SignalMachineStartRoad, sm.SignalMachineEndRoad) like '%" + dtRequestDto.getSearch() + "%' AND mf.MachineCheck in (1,2) ";
        //根据维修人员和系统管理员区分
        if (machineMaintenanceStatus != 3) {
            sql += " and mf.machineMaintenanceStatus = " + machineMaintenanceStatus + " ";
        }

        if (actorIds.indexOf('1') >= 0) {
            sql += "";
        } else if (actorIds.indexOf('3') >= 0 || actorIds.indexOf('5') >= 0) {
            sql += " AND mf.MachineOrgId =" + orgId + "";
        } else if (actorIds.indexOf('7') >= 0) {
            sql += " AND mf.MachineMainOrgId =" + orgId + "";
        }
        sql += " order by mf.machineMaintenanceStatus ASC ";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(DTMachineFaultDto.class));
        List<DTMachineFaultDto> solarFaultDtos = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return solarFaultDtos;
    }

    public Integer getSignalMachineCount4DT(DTRequestDto dtRequestDto, String actorIds, Integer orgId, Integer machineMaintenanceStatus) throws Exception{
        String sql = "SELECT count(*)\n" +
                "FROM tb_signal_machine sm,tb_machine_fault mf,tb_org o\n" +
                "WHERE mf.SignalMachineId=sm.SignalMachineId AND mf.MachineOrgId=o.OrgId AND mf.MachineMainCheck=1 " +
                "AND concat(sm.SignalMachineStartRoad, sm.SignalMachineEndRoad) like '%" + dtRequestDto.getSearch() + "%' AND mf.MachineCheck=1 ";
        //根据维修人员和系统管理员区分
        if (machineMaintenanceStatus != 3) {
            sql += " and mf.machineMaintenanceStatus = " + machineMaintenanceStatus + " ";
        }
        if (actorIds.indexOf('1') >= 0) {
            sql += "";
        } else if (actorIds.indexOf('3') >= 0 || actorIds.indexOf('5') >= 0) {
            sql += " AND mf.MachineOrgId =" + orgId + "";
        } else if (actorIds.indexOf('7') >= 0) {
            sql += " AND mf.MachineMainOrgId =" + orgId + "";
        }
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public List<SignalMachineFaultDto> getMachineFaultCheckList4App(Integer orgId, String actorIds, Integer faultStatus,
                                                                    Integer count, Integer size) throws Exception{
        String sql = " SELECT mf.MachineFaultId,mf.SignalMachineId,mf.MachinePhoto,mf.MachineType,mf.MachineLevel," +
                "mf.MachineReason,mf.MachineTime,mf.MachineCheck,mf.MachineMainCheck,mf.MachineMainCheckTime\n" +
                "FROM tb_machine_fault mf ";
        if (actorIds.indexOf("1") >= 0) {//系统管理员
            if (faultStatus == 0) {//待审核
                sql += " WHERE (mf.MachineCheck =0) OR (mf.MachineMainCheck =0 AND mf.MachineCheck=1 )  ";
            } else if (faultStatus == 1) {//已通过
                sql += " WHERE  mf.MachineMainCheck =1 AND mf.MachineCheck=1  ";
            } else if (faultStatus == 2) {//未通过
                sql += " WHERE ( mf.MachineCheck =-1) OR (mf.MachineMainCheck =-1 AND mf.MachineCheck=1 ) ";
            } else {
                return null;
            }
        } else if (actorIds.indexOf("3") >= 0) {//审核员
            if (faultStatus == 0) {//待审核
                sql += " WHERE mf.MachineCheck=0 AND mf.MachineOrgId=" + orgId + " ";
            } else if (faultStatus == 1) {//已通过
                sql += " WHERE mf.MachineCheck=1 AND mf.MachineOrgId=" + orgId + " ";
            } else if (faultStatus == 2) {//未通过
                sql += " WHERE mf.MachineCheck=-1 AND mf.MachineOrgId=" + orgId + " ";
            } else {
                return null;
            }
        } else if (actorIds.indexOf("7") >= 0) {//主管审核员
            if (faultStatus == 0) {//待审核
                sql += " WHERE mf.MachineCheck=1 AND mf.MachineMainCheck=0 AND mf.MachineMainOrgId=" + orgId + " ";
            } else if (faultStatus == 1) {//已通过
                sql += " WHERE mf.MachineCheck=1 AND mf.MachineMainCheck=1 AND mf.MachineMainOrgId=" + orgId + " ";
            } else if (faultStatus == 2) {//未通过
                sql += " WHERE mf.MachineCheck=1 AND mf.MachineMainCheck=-1 AND mf.MachineMainOrgId=" + orgId + " ";
            } else {
                return null;
            }
        }
        sql += " ORDER BY mf.MachineTime DESC ";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(SignalMachineFaultDto.class));
        return query.setFirstResult((count - 1) * size).setMaxResults(size).getResultList();
    }


    public List<SignalMachineFaultDto> getMachineFaultHandleList4App(Integer orgId, Integer maintenanceStatus, String actorIds, Integer count, Integer size)throws Exception{
        String sql = "SELECT mf.MachineFaultId,mf.SignalMachineId,mf.MachinePhoto,mf.MachineType,mf.MachineLevel," +
                "mf.MachineReason,mf.MachineMaintenanceStatus,mf.MachineMainCheckTime,mf.MachineMaintenancePhoto," +
                "mf.MachineMaintenanceTime\n" +
                "FROM tb_machine_fault mf " +
                "WHERE mf.MachineMaintenanceStatus=" + maintenanceStatus + "";
        if (actorIds.indexOf("7") >= 0) {
            sql += " AND mf.MachineMainOrgId=" + orgId + "";
        } else if (actorIds.indexOf("5") >= 0) {
            sql += " AND mf.MachineOrgId=" + orgId + "";
        }
        sql += " ORDER BY mf.MachineMainCheckTime DESC ";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(SignalMachineFaultDto.class));
        return query.setFirstResult((count - 1) * size).setMaxResults(size).getResultList();
    }

    public List<SignalMachineFaultDto> getMachineFaultList4App(Integer orgId, String actorIds, Integer faultStatus, Integer count, Integer size) throws Exception{
        String sql = "SELECT mf.MachinePhoto,mf.SignalMachineId,mf.MachineType,mf.MachineReason,mf.MachineFaultId,mf.MachineLevel,mf.MachineTime,mf.MachineCheck,mf.MachineMainCheck\n" +
                "FROM tb_signal_machine sm,tb_machine_fault mf\n" +
                "WHERE sm.SignalMachineId=mf.SignalMachineId\n";
        //-1:未通过；0：待审核；1：已通过
        if(faultStatus==-1){
            sql += " AND (mf.MachineCheck=-1 OR mf.MachineMainCheck=-1) ";
        }else if(faultStatus==0){
            sql += " AND ((mf.MachineCheck=1 AND mf.MachineMainCheck=0) OR mf.MachineCheck = 0) ";
        }else if(faultStatus==1){
            sql += " AND mf.MachineMainCheck = 1 ";
        }
        if (actorIds.indexOf("1") >= 0) {
            sql += "";
        } else {
            sql += " AND mf.MachineOrgId = " + orgId + " ";
        }
        sql += "ORDER BY MachineTime DESC";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(SignalMachineFaultDto.class));
        return query.setFirstResult((count - 1) * size).setMaxResults(size).getResultList();
    }
}
